package com.spider.oss;

import com.spider.util.JdbcUtilV5;
import com.spider.util.JdbcUtilV8;
import lombok.extern.slf4j.Slf4j;
import org.springframework.stereotype.Service;

import java.sql.*;

@Service
@Slf4j
public class OssServiceImpl {
    //@Autowired
    JdbcUtilV8 jdbcUtilV8;
    public void jdbcBatchInsertV5(FileInfo file) {
        Connection conn = null;
        PreparedStatement selectStmt = null;
        PreparedStatement insertStmt = null;
        ResultSet rs = null;

        try {
            conn = JdbcUtilV5.getConnection();
            conn.setAutoCommit(false); // 关闭自动提交

            // 查询 SQL
            String selectSql = "SELECT COUNT(*) FROM batch WHERE filename = ?";
            selectStmt = conn.prepareStatement(selectSql);
            selectStmt.setString(1, file.getFilename());
            rs = selectStmt.executeQuery();

            if (rs.next()) {
                int count = rs.getInt(1);
                if (count == 0) {
                    // 没有对应记录，进行插入操作
                    String insertSql = "INSERT INTO batch (filename, url, upload_time) VALUES (?, ?, ?)";
                    insertStmt = conn.prepareStatement(insertSql);
                    insertStmt.setString(1, file.getFilename());
                    insertStmt.setString(2, file.getUrl());
                    insertStmt.setTimestamp(3, new Timestamp(System.currentTimeMillis()));
                    insertStmt.executeUpdate();
                    conn.commit();
                    log.info("已插入记录: {}", file.getFilename());
                } else {
                    log.info("记录已存在，跳过插入: {}", file.getFilename());
                }
            }
        } catch (SQLException e) {
            try {
                if (conn != null) conn.rollback();
            } catch (SQLException ex) {
                log.error("回滚失败: {}", ex.getMessage());
            }
            log.error("SQL 异常: [{}] {}", e.getErrorCode(), e.getMessage());
        } finally {
            JdbcUtilV5.close(conn, selectStmt, rs);
            JdbcUtilV5.close(null, insertStmt, null);
        }
    }

    /**
     * 添加阿尔塔网站前端的coa文件
     * @param file
     */
    public void jdbcBatchInsertWebCmsCert(FileInfo file) {
        Connection conn = null;
        PreparedStatement selectStmt = null;
        PreparedStatement insertStmt = null;
        ResultSet rs = null;

        try {
            conn = jdbcUtilV8.getConnection();
            conn.setAutoCommit(false); // 关闭自动提交

            // 查询 cms_cert 表中是否存在该 batch（文件名）的记录
            String selectSql = "SELECT COUNT(*) FROM cms_cert WHERE batch = ?";
            selectStmt = conn.prepareStatement(selectSql);
            selectStmt.setString(1, file.getFilename());
            rs = selectStmt.executeQuery();

            if (rs.next()) {
                int count = rs.getInt(1);
                if (count == 0) {
                    // 没有对应记录，进行插入操作到 cms_cert 表
                    String insertSql = "INSERT INTO cms_cert (batch, phone, code, name, url, type, del_flag) VALUES (?, NULL, '1', NULL, ?, 0, 0)";
                    insertStmt = conn.prepareStatement(insertSql);
                    insertStmt.setString(1, file.getFilename());
                    insertStmt.setString(2, file.getUrl());

                    insertStmt.executeUpdate();
                    conn.commit();
                    log.info("已插入记录到 cms_cert 表: {}", file.getFilename());
                } else {
                    log.info("记录已存在于 cms_cert 表，跳过插入: {}", file.getFilename());
                }
            }
        } catch (SQLException e) {
            try {
                if (conn != null) conn.rollback();
            } catch (SQLException ex) {
                log.error("回滚失败: {}", ex.getMessage());
            }
            log.error("SQL 异常: [{}] {}", e.getErrorCode(), e.getMessage());
        } finally {
            jdbcUtilV8.close(conn, selectStmt, rs);
            jdbcUtilV8.close(null, insertStmt, null);
        }
    }

}
